﻿-- =============================================
-- Author:		GSJ
-- Create date: 2015-01-19
-- Description:	月报查询
-- =============================================
CREATE PROCEDURE [dbo].[proc_FinanceMonthTable_Select20150119]
	(
		@UserId NCHAR(10),
		@StartDate NCHAR(10),
		@EndDate NCHAR(10)	
	)
AS
BEGIN
	
	DECLARE @cid VARCHAR(8000)
	SELECT @cid=au.Cid FROM aspnet_Users au where au.username =@UserId
	IF @cid='[ALL]'
	BEGIN
		  SELECT 
 
  	@cid=
  	REPLACE(REPLACE(( SELECT id FROM Company  
                                        order by id asc FOR XML AUTO ), '<Company id="', '['), '"/>', ']')
	END
	ELSE IF @cid=''
	BEGIN
		SELECT @cid= '['+Convert(varchar,au.CompanyId)+']'
		  FROM aspnet_Users au where au.username =@UserId
	END

	PRINT @CID
	
	;WITH list AS (
SELECT 
	  maid,
      MAname
      ,sum(bd) AS bd
      ,sum(wz)AS wz
      ,sum(qt)AS qt
      ,sum(xfml)AS xfml
      ,sum(xsml)AS xsml
      ,sum(xse)AS xse
	  ,sum(xse)+isnull(sum(qqqdfqzcxse),0) AS xsze
      ,sum(mlhj)AS mlhj
	  ,sum(mlhj)+isnull(sum(qqqdfqzcml),0) AS mlzhj
      ,sum(xzys)AS xzys
      ,sum(wksr)AS wksr
      ,sum(tx)AS tx
      ,sum(rcfy)AS rcfy
      ,sum(cl)AS cl
      ,sum(sscl)+isnull((select Sum(amount) from Finance_Profit_Regulate 
					where Mid=f.maid and CONVERT(varchar(10),RegulateDate,120) BETWEEN @StartDate AND @EndDate and Deleted=0 and Approval=1),0) AS sscl
      ,sum(zxf)AS zxf
      ,sum(albb) AS albb,
      sum(qyqq) AS qyqq,
	  ISNULL(SUM(QYQQ_CustomerCount),0) AS QYQQ_CustomerCount
      ,sum(gdzc) as gdzc
      ,sum(yggz) as yggz
	  ,sum(ygbx) as ygbx
      ,sum(sf) as sf
      ,sum(ff) as ff
      ,sum(zbfy) as zbfy
      ,sum(ygfl) as ygfl
       ,sum(sjglf) as sjglf
        ,sum(qtzc) as qtzc
         ,sum(fyze) as fyze
          ,sum(fh) as fh
           ,sum(ssclwsr) as ssclwsr
            ,sum(fyyxsr) as fyyxsr
             ,isnull(sum(xfxse),0) as xfxse
             ,isnull(sum(xzxse),0) as xzxse
             ,isnull(sum(bd_je),0) as bd_je
             ,isnull(sum(wz_je),0) as wz_je
             ,isnull(sum(qyQQ_je),0) as qyQQ_je
             ,isnull(sum(ssclwzc),0) as ssclwzc
             ,isnull(sum(jkzc),0) as jkzc
             ,isnull(sum(jksr),0) as jksr
             ,isnull(sum(dkzc),0) as dkzc
             ,isnull(sum(dksr),0) as dksr
             ,isnull(sum(tzzc),0) as tzzc
             ,isnull(sum(tzsr),0) as tzsr
             ,isnull(sum(idc),0) as idc
             ,isnull(sum(idc_je),0) as idc_je
             ,isnull(sum(qt_je),0) as qt_je
             ,isnull(sum(jj),0) as jj
			 ,isnull(sum(bcfk),0) as bcfk
			 ,isnull(sum(kqfk),0) as kqfk
			,isnull(sum(qqqdfqzcml),0) as qqqdfqzcml
			,isnull(sum(qqqdfqzcxse),0) as qqqdfqzcxse,
			isnull(sum(bdztc),0) as bdztc,
			isnull(sum(bdztcxse),0) as bdztcxse,
			isnull(sum(dhkdf),0) as dhkdf,
		   isnull(sum(sdf),0) as sdf,
		   isnull(sum(bgfy),0) as bgfy,
		   isnull(sum(clf),0) as clf,
		   isnull(sum(zdf),0) as zdf,
		   isnull(sum(cwfy),0) as cwfy,
		   isnull(sum(zppxf),0) as zppxf,
		   isnull(sum(scxcf),0) as scxcf,
		   isnull((select Sum(amount) from Finance_Profit_Regulate 
					where Mid=f.maid and CONVERT(varchar(10),RegulateDate,120) BETWEEN @StartDate AND @EndDate and Deleted=0 and Approval=1),0) sscltz 
 FROM FinanceMonthTable f WHERE 
 tmonth BETWEEN @StartDate AND @EndDate and 
 
 --MAid IN  (SELECT c.Mid FROM Company c WHERE CHARINDEX('['+CONVERT(VARCHAR, c.Id)+']' , 
 --                       (SELECT au.Cid FROM aspnet_Users au where au.username =@UserId),0)>0) 
 CHARINDEX('['+CONVERT(VARCHAR, CompanyId)+']' , 
                       --(SELECT au.Cid FROM aspnet_Users au where au.username =@UserId)
                      @cid ,0)>0
 
GROUP BY maid,MAname
 )

SELECT * FROM list,(SELECT SUM(bd) AS bd1,
      SUM([wz]) AS wz1,
      SUM([qt]) AS qt1,
      SUM([xfml]) AS xfml1,
      SUM([xsml]) AS xsml1,
      SUM([xse]) AS xse1,
	  SUM([xsze]) AS xsze1,
      SUM([mlhj]) AS mlhj1,
	  SUM([mlzhj]) AS mlzhj1,
      SUM([xzys]) AS xzys1,
      SUM([wksr]) AS wksr1,
      SUM([tx]) as tx1,
      SUM([rcfy]) AS rcfy1,
      SUM([cl]) AS cl1,
      SUM([sscl])+sum(sscltz) AS sscl1,
      SUM([zxf]) as zxf1 
      ,sum(albb) AS albb1,
      sum(qyqq) AS qyqq1,
	  SUM(QYQQ_CustomerCount) AS QYQQ_CustomerCount1
      ,sum(yggz) as yggz1
	  ,sum(ygbx) as ygbx1
      ,sum(sf) as sf1
      ,sum(ff) as ff1
      ,sum(zbfy) as zbfy1
      ,sum(ygfl) as ygfl1
       ,sum(sjglf) as sjglf1
        ,sum(qtzc) as qtzc1
         ,sum(fyze) as fyze1
          ,sum(fh) as fh1
           ,sum(ssclwsr) as ssclwsr1
            ,sum(fyyxsr) as fyyxsr1
             ,sum(xfxse) as xfxse1
             ,sum(xzxse) as xzxse1
             ,sum(bd_je) as bd_je1
             ,sum(wz_je) as wz_je1
             ,sum(qyQQ_je) as qyQQ_je1
             ,sum(ssclwzc) as ssclwzc1
             ,sum(jkzc) as jkzc1
             ,sum(jksr) as jksr1
             ,sum(dkzc) as dkzc1
             ,sum(dksr) as dksr1
             ,sum(tzzc) as tzzc1
             ,sum(tzsr) as tzsr1
             ,sum(idc) as idc1
             ,sum(idc_je) as idc_je1
             ,sum(qt_je) as qt_je1
             ,SUM(gdzc) as gdzc1 
             ,SUM(jj) as jj1 
			 ,SUM(bcfk) as bcfk1
			 ,SUM(kqfk) as kqfk1 
			 ,SUM(qqqdfqzcml) as qqqdfqzcml1 
			 ,SUM(qqqdfqzcxse) as qqqdfqzcxse1,
			 sum(bdztc) bdztc1,
			 sum(bdztcxse) bdztcxse1,
			 sum(dhkdf) as dhkdf1,
		     sum(sdf) as sdf1,
		     sum(bgfy) as bgfy1,
		     sum(clf) as clf1,
		     sum(zdf) as zdf1,
		     sum(cwfy) as cwfy1,
		     sum(zppxf) as zppxf1,
		     sum(scxcf) as scxcf1,
			 sum(sscltz) as sscltz1 
                    FROM list            
                    ) AS n
      
       ORDER BY maid
END